-- @owner: songjing20
-- @date: 2024-7-12
-- @testpoint: DQL语法，connect by基表为连接表且连接条件有过滤时结果正确

--step1:建表t_connect_001并插入数据，expect: 成功
drop table if exists t_connect_001;
create table t_connect_001(id int, lid int, name text);
insert into t_connect_001 values(1,null,'A'),(2,1,'B'),(3,2,'C');

--step2:建表t_connect_002并插入数据，expect: 成功
drop table if exists t_connect_002;
create table t_connect_002(idd int, lidd int, name text);
insert into t_connect_002 values(1,null,'A'),(2,1,'B'),(3,2,'C'), (4,3,'D');

--step3:验证connect by基表为连接表且连接条件有过滤时结果正确,expect: 成功
select * from t_connect_002,t_connect_001
where t_connect_001.id=t_connect_002.idd
and t_connect_001.id!=2
start with id=2 connect by prior id=lid;

--step4: 清理环境， expect: 成功
drop table t_connect_001;
drop table t_connect_002;
